package com.leeson.portal.manage.utils;


import java.io.File;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;






import org.apache.commons.codec.digest.DigestUtils;

import com.leeson.portal.manage.domain.Account;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.write.DateFormat;
import jxl.write.DateTime;
import jxl.write.Label;
import jxl.write.NumberFormat;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

/**
 * 
* This class is used for ...  操作excel
* @author LeeSon  QQ:25901875
* @version 1.0, 2015年8月7日 上午8:42:13
 */
public class ExcelUtils {
	private static SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss"); 
	 /**
     * 到处Account列表到excel文件
     * @param fileName 文件路径+文件名+文件后缀
     */
	public static void writeAccountToExcel(String fileName,List<Account> accounts) throws Exception{
		
		jxl.write.Number n = null;
		jxl.write.DateTime d = null;
		
		File tempFile = new File(fileName);
		WritableWorkbook workbook = Workbook.createWorkbook(tempFile);
		WritableSheet sheet = workbook.createSheet("OpenPortalServer接入账户列表",0);
		
		// 预定义的一些字体和格式， 字形、大小、加粗、倾斜、下划线、颜色
		// 头文件
		WritableFont headerFont = new WritableFont(WritableFont.ARIAL,15,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.GREEN);
		WritableCellFormat headerFormat = new WritableCellFormat(headerFont);
		
		// 标题
		WritableFont titleFont = new WritableFont(WritableFont.ARIAL,10,WritableFont.NO_BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.RED);
		WritableCellFormat titleFormat = new WritableCellFormat(titleFont);
		
		// 内容
		WritableFont detFont = new WritableFont(WritableFont.ARIAL,10,WritableFont.NO_BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.BLACK);
		WritableCellFormat detFormat = new WritableCellFormat(detFont);
		
		// number 格式
		NumberFormat nf=new NumberFormat("0");
		WritableCellFormat priceFormat = new WritableCellFormat(nf);
		
		// 日期
		DateFormat df = new DateFormat("yyyy-MM-dd hh:mm:ss");
		WritableCellFormat dateFormat = new WritableCellFormat(df);
		
		// 创建单元格
		Label l = new Label(0,0,"OpenPortalServer系统接入账户列表",headerFormat);
		sheet.addCell(l);
		
		// 添加标题
		int column=0; //　列
		l = new Label(column++,1,"序号",titleFormat);
		sheet.addCell(l);
		l = new Label(column++,1,"登录名",titleFormat);
		sheet.addCell(l);
		l = new Label(column++,1,"姓名",titleFormat);
		sheet.addCell(l);
		l = new Label(column++,1,"性别",titleFormat);
		sheet.addCell(l);
		l = new Label(column++,1,"电话号码",titleFormat);
		sheet.addCell(l);
		l = new Label(column++,1,"电子邮件",titleFormat);
		sheet.addCell(l);
		l = new Label(column++,1,"说明",titleFormat);
		sheet.addCell(l);
		l = new Label(column++,1,"类别",titleFormat);
		sheet.addCell(l);
		l = new Label(column++,1,"买断到期",titleFormat);
		sheet.addCell(l);
		l = new Label(column++,1,"剩余时长(分钟)",titleFormat);
		sheet.addCell(l);
		
		int y=accounts.size();  // 行
		for(int i=0;i<y;i++){
			column = 0; // 列
			l = new Label(column++,i+2,String.valueOf(i+1),detFormat);
			sheet.addCell(l);
			l = new Label(column++,i+2,accounts.get(i).getLoginName(),detFormat);
			sheet.addCell(l);
			l = new Label(column++,i+2,accounts.get(i).getName(),detFormat);
			sheet.addCell(l);
			
			String sex;
			if(accounts.get(i).getGender().equals(String.valueOf(0))){
				sex="女";
			}else if (accounts.get(i).getGender().equals(String.valueOf(1))) {
				sex="男";
			}else {
				sex="";
			}
			l = new Label(column++,i+2,sex,detFormat);
			sheet.addCell(l);
			
			l = new Label(column++,i+2,accounts.get(i).getPhoneNumber(),detFormat);
			sheet.addCell(l);
			l = new Label(column++,i+2,accounts.get(i).getEmail(),detFormat);
			sheet.addCell(l);
			l = new Label(column++,i+2,accounts.get(i).getDescription(),detFormat);
			sheet.addCell(l);
			
			String state;
			if(accounts.get(i).getState().equals(String.valueOf(1))){
				state="免费";
			}else if (accounts.get(i).getState().equals(String.valueOf(2))) {
				state="计时";
			}else if (accounts.get(i).getState().equals(String.valueOf(3))) {
				state="买断";
			}else {
				state="停用";
			}
			l = new Label(column++,i+2,state,detFormat);
			sheet.addCell(l);
			
			d = new DateTime(column++,i+2,accounts.get(i).getDate(),dateFormat);
			sheet.addCell(d);
			
			n = new jxl.write.Number(column++,i+2,accounts.get(i).getTime()/1000/60,priceFormat);
			sheet.addCell(n);
		}
		
		
		// 设置列的宽度
		column=0;
		sheet.setColumnView(column++, 10);
		sheet.setColumnView(column++, 20);
		sheet.setColumnView(column++, 20);
		sheet.setColumnView(column++, 10);
		sheet.setColumnView(column++, 20);
		sheet.setColumnView(column++, 20);
		sheet.setColumnView(column++, 20);
		sheet.setColumnView(column++, 10);
		sheet.setColumnView(column++, 20);
		sheet.setColumnView(column++, 15);
		
		workbook.write();
		workbook.close();
		System.out.println("用户列表写入"+fileName+"成功！");
	}
	
	/**
	 * 读取文件信息
	 * @param fileName
	 */
	public static List<Account> readExcelAccount(String fileName)throws Exception{
		List<Account> accounts=new ArrayList<Account>();
		Workbook book = Workbook.getWorkbook(new File(fileName)); // 构造Workbook（工作簿）对象
		
		Sheet sheet = book.getSheet(0);
		// 得到第一列第一行的单元格
		int columNum = sheet.getColumns(); // 得到所有列
		int rowNum = sheet.getRows(); // 得到所有行
		
		System.out.println("行："+rowNum+"  列："+columNum);
		
		for(int i=2;i<rowNum;i++){// 读取行
			Account account=new Account();
			for(int j=1;j<columNum;j++){ //读取列
				Cell cell = sheet.getCell(j, i);
				String result = cell.getContents(); // 得到单元格的值
				System.out.print(result);
				System.out.print("\t");
				if(j==1){
					account.setLoginName(result);
				}
				if(j==2){
					account.setName(result);
				}
				if(j==3){
					if(result.equals("男")){
						account.setGender(String.valueOf(1));
					}else if(result.equals("女")){
						account.setGender(String.valueOf(0));
					}
				}
				if(j==4){
					account.setPhoneNumber(result);
				}
				if(j==5){
					account.setEmail(result);
				}
				if(j==6){
					account.setDescription(result);
				}
				if(j==7){
					account.setState(result);
				}
				if(j==8){
					account.setDate(format.parse(result));
				}
				if(j==9){
					account.setTime(Long.parseLong(result)*60*1000);
				}
				if(j==10){
					String md5Digest;
					if(result==null||result.equals("")){
						md5Digest = DigestUtils.md5Hex("1234");
					}else {
						md5Digest = DigestUtils.md5Hex(result);
					}
					account.setPassword(md5Digest);
				}
				
				
			}
			System.out.println();
			accounts.add(account);
		}
		book.close(); // 关闭对象
		
		return accounts;
	}
	
	
}
